import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author yoann
 */
class DBConnection {

        static Connection conn = null ;
        private static Statement stmt = null;
        private static ResultSet rs = null;
        private static String query = null ;


    /** Connection to the database   **/
	public static void connect() throws Exception {
            if(!isConnectionStarted()){
		try {
			//String url = "jdbc:mysql://db-und.ida.liu.se/abdri234";
			//String utilisateur = "abdri234";
			//String mdp = "abdri234118a";
                        String url = "jdbc:mysql://db-und.ida.liu.se/abdri234";
			String utilisateur = "abdri234";
			String mdp = "abdri234118a";
                        println("connected???")
			MysqlDataSource ods = new MysqlDataSource();
			ods.setUser(utilisateur);
			ods.setPassword(mdp);
			ods.setURL(url);
			conn = ods.getConnection();

                        println("yes")

		} catch (SQLException e) {
			throw new Exception ("connexion impossible \n" + e.getMessage() + " code oracle " + e.getErrorCode());
		}
            }
	}

    /** closes all connections with the database **/
    public static void closeAll(){
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				;
			}
			rs = null;
		}
		if (stmt != null) {
			try {
				stmt.close();
			}
			catch (SQLException e) {
				;
			}
			stmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				;
			}
			conn = null;
		}
	}

    /** tests whether a transaction is opened or not **/
    public static boolean isConnectionStarted(){
        return (conn!=null);
    }


    /** start a new transaction **/
    public static void startTransaction(){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "start transaction";
            stmt.executeQuery(query);
            println("transaction started!")
        }
    }

    /** rollback the running transaction */
    public static void rollbackTransaction(String currentMode){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "rollback";
            stmt.executeQuery(query);
            println("RolledBack!")
            closeAll()
            println("mode:"+currentMode)
            if(currentMode==null || currentMode.equalsIgnoreCase("sandbox")){
                DBConnection.connect()
                DBConnection.startTransaction()
            }
        }
    }

    /** commits the transaction, the current decides whether we should restart a new transaction (sandbox mode) after the commit **/
    public static void commitTransaction(String currentMode){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "commit";
            stmt.executeQuery(query);
            println("commited!")
            closeAll()
            if(currentMode==null || currentMode.equalsIgnoreCase("sandbox")){
                DBConnection.connect()
                DBConnection.startTransaction()
            }
        }
    }

    /** changes the engine of the given table **/
    public static void alterTableEngine(String table){
        stmt = conn.createStatement();
        query = "alter table "+table+" engine=InnoDB"
        stmt.executeUpdate(query);
    }


    
    /**************************************************************************/
    /*******         Access to the database concerning projects         *******/
    /**************************************************************************/

    /** get the project specified by its Id and create it as an object **/
    public static Project getProject(String project){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "select * from project where id="+project;
            rs = stmt.executeQuery(query);
            rs.next()
            Area a = Area.get(rs.getString(4))
            Project p = new Project(area:a,activityId:rs.getString(3),description:rs.getString(5),name:rs.getString(8),mode:rs.getString(7),startDate:new Date(rs.getString(9).substring(0,10).replace('-','/')),endDate:new Date(rs.getString(6).substring(0,10).replace('-','/')))
            p.id=Long.parseLong(rs.getString(1))
            //println("project got")
            for(Whoa w :getWhoaList(p)){
                p.addToAmountOfHour(w);
            }
            return p
        }
    }

    /** update the given project **/
    public static void updateProject(Project p){
 
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "update project set name='"+p.name+"', "
            query += "activity_id='"+p.activityId+"', "
            query += "area_id='"+p.area.id+"', "
            query += "description='"+p.description+"', "
            query += "end_date='"+p.endDate.format('yy/MM/dd')+"', "
            query += "name='"+p.name+"', "
            query += "start_date='"+p.startDate.format('yy/MM/dd')+"'"
            query += " where id="+p.id;
            stmt.executeUpdate(query);

            //println("executed 1")
            stmt = conn.createStatement();
            query = "update project set activity_id='"+p.activityId+"' where id="+p.id;
            stmt.executeUpdate(query);
            //println("executed 2")
            DBConnection.updateAmountOfHour(p)

            //println("updated!")
            //showvalue()
        }
    }

    /** gets the list of projects **/
    public static List<Project> getProjectList(){
        ArrayList<Project> l = new ArrayList<Project>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id from project";
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
               l.add(DBConnection.getProject(rs2.getString(1)))
            }
        }
        return l
    }

    /** get the count of project **/
    public static Integer getProjectCount(){
        ArrayList<Project> l = new ArrayList<Project>()
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select count(id) from project";
            rs = stmt.executeQuery(query);
            rs.next()
            return Integer.parseInt(rs.getString(1))
        }
        else{
            return 0
        }
    }

    /** get a list of projects for a specific area **/
    public static List<Project> findProjectByArea(Area a){
        ArrayList<Project> l = new ArrayList<Project>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id from project where area_id="+a.id;
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
               l.add(DBConnection.getProject(rs2.getString(1)))
            }
        }
        return l
    }

    /** creates the given project in the database **/
    public static Integer saveProject(Project p){
        try{
            stmt = conn.createStatement();
            query = "insert into project values(0,0,'"+p.activityId+"',"+p.area.id+",'"+p.description+"','"+p.endDate.format('yy/MM/dd')+"',"+p.mode+",'"+p.name+"','"+p.startDate.format('yy/MM/dd')+"',null,null)";
            //query = "insert into project values(0,0,'"+p.activityId+"',"+p.area.id+",'"+p.description+"','"+p.endDate.format('yy/MM/dd')+"',"+p.mode+",'"+p.name+"','"+p.startDate.format('yy/MM/dd')+"')";
            stmt.execute(query);
            println("saveP 0")
//            if(DBConnection.updateAmountOfHour(p)<0){
//                return -1
//            }
            println("saveP 1")
            stmt = conn.createStatement();
            query = "select max(id) from project";
            rs = stmt.executeQuery(query);
            rs.next()
            println("inserted")
            return Integer.parseInt(rs.getString(1))
        }
        catch(Exception e){
            println("exc Save project:"+e)
            return -1
        }
    }

    /** delete the project with the given Id from the database **/
    public static boolean deleteProject(Long pId){
        try{
            stmt = conn.createStatement();
            query = "delete from project where id="+pId;

            stmt.execute(query);
            println("project deleted")
            return true
        }
        catch(Exception e){
            println("exc:"+e)
            return false
        }
    }


    /**************************************************************************/
    /*******        Access to the database concerning employees         *******/
    /**************************************************************************/

    /**
    * This function returns the list of all employees
    **/
    public static List<Employee> getEmployeeList(){
        List<Employee> res = new ArrayList<Employee>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id from employee";
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
               res.add(DBConnection.getEmployee(rs2.getString(1)))
            }
        }
        return res
    }

    /** get the employee specified by its Id and create it as an object **/
    public static Employee getEmployee(String employee){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "select * from employee where id="+employee;
            //println("quer:"+query)
            rs = stmt.executeQuery(query);
            rs.next()
            Area a = Area.get(rs.getString(8))
            Employee e = new Employee(hiredBy:a,employeeId:rs.getString(6),firstName:rs.getString(7),lastName:rs.getString(9),dob:new Date(rs.getString(4).substring(0,10).replace('-','/')),title:rs.getString(15),salary:rs.getString(13),email:rs.getString(5),street:rs.getString(14),phoneNumber:rs.getString(12),city:rs.getString(3),zipCode:rs.getString(16),mode:rs.getString(10))
            e.id=Integer.parseInt(rs.getString(1))
            return e
        }
    }

    /**************************************************************************/
    /*******          Access to the database concerning Whoa            *******/
    /**************************************************************************/
    
    /**
    * This function returns the list of Whoa for the project with the given Id
    **/
    public static List<Whoa> getWhoaList(Activity p){
        List<Whoa> res = new ArrayList<Whoa>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id from whoa where works_in_project_id="+p.id;
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
               Whoa w = DBConnection.getWhoa(rs2.getString(1))
               w.worksInProject = p
               res.add(w)
            }
        }
        return res
    }

    /** get the whoa specified by its Id and create it as an object **/
    public static Whoa getWhoa(String whoa){
        if(isConnectionStarted()){
            ResultSet rs3
            stmt = conn.createStatement();
            query = "select * from whoa where id="+whoa;
            rs3 = stmt.executeQuery(query);
            rs3.next()
            Employee e = DBConnection.getEmployee(rs3.getString(4))
            //Project p = DBConnection.getProject(rs.getString(6))
            Whoa w = new Whoa(member:e,year:(1900+Integer.parseInt(rs3.getString(7))),hours:Double.parseDouble(rs3.getString(3)))
            w.id=Integer.parseInt(rs3.getString(1))
            return w
        }
    }
    
    /**
     * update the amount of hour for a given project
     **/
    public static Integer updateAmountOfHour(Project p){
        try{
            //test if we need to delete some whoa
            //println("project to update:"+p)
            List<Whoa> temp = DBConnection.getWhoaList(p)
            //println("list:"+temp)
            for( Whoa w : temp  ){
                //println("boucle")
                boolean found=false
                for( Whoa w1 : p.amountOfHour){
                    if(w1.id==w.id){
                        found=true
                        break
                    }
                }
                if(!found){
                    DBConnection.deleteWhoa()
                }
            }
            println("ici"+p.amountOfHour)
            //add all the current whoa
            if(p.amountOfHour && p.amountOfHour.size()>0){
                println("laaa")
                for(Whoa w : p.amountOfHour){
                    if(DBConnection.getWhoa(w.id)){ //if it exists already
                        println("oh:"+p.amountOfHour)
                        //update it
                        DBConnection.updateWhoa(w)
                        println("whoa updated")
                    }
                    else{ //if it doesn't exists
                        //create it
                        println("create")
                        if(DBConnection.saveWhoa(w,p)<0){
                            println("err:l 375 DBConnection")
                            return -1
                        }
                    }
                }
            }
        }
        catch(Exception e){
            println("exc updateAmountOfHour:"+e)
            return -1
        }
        
    }

    /** creates the given whoa in the database **/
    public static Integer saveWhoa(Whoa w, Project p){
        try{
            stmt = conn.createStatement();
            println("whoa:"+w)
            println("project whoa:"+p)
            //query = "insert into project values(0,0,'"+p.activityId+"',"+p.area.id+",'"+p.description+"','"+p.endDate.format('yy/MM/dd')+"',"+p.mode+",'"+p.name+"','"+p.startDate.format('yy/MM/dd')+"',null,null)";
            //query = "insert into whoa values(0,0,"+w.hours+","+w.member.id+",null,"+p.id+","+w.year+")";
            query = "insert into whoa values(0,0,"+w.hours+","+w.member.id+",null,"+p.id+","+w.year+",null,null)";
            stmt.execute(query);

            stmt = conn.createStatement();
            query = "select max(id) from whoa";
            rs = stmt.executeQuery(query);
            rs.next()
            println("whoa inserted")
            return Integer.parseInt(rs.getString(1))
        }
        catch(Exception e){
            println("exc:"+e)
            return -1
        }
    }

    /** updates the given whoa in the database **/
    public static Integer updateWhoa(Whoa w){
        try{
            if(isConnectionStarted()){
                stmt = conn.createStatement();
                query = "update whoa set hours="+w.hours+", "
                query += "member_id="+w.member.id+", "
                query += "works_in_course_id="+w.worksInCourse.id+", "
                query += "works_in_project_id="+w.worksInProject.id+", "
                query += "year="+w.year+", "
                query += " where id="+w.id;
                stmt.executeUpdate(query);
                println("Whoa updated!")
                return 1
            }
            else{
                return -1
            }
        }
        catch(Exception e){
            println("exc:"+e)
            return -1
        }
    }

    /** delete the given whoa from the database **/
    public static Integer deleteWhoa(Whoa w){
        try{
            stmt = conn.createStatement();
            query = "delete from whoa where id="+w.id;

            stmt.execute(query);
            println("whoa deleted")
            return true
        }
        catch(Exception e){
            println("exc:"+e)
            return -1
        }
    }
}

